package dao.impl;

import dao.StuDao;
import domain.Stu;
import domain.Student;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import utils.JDBCUtils;

import java.util.List;
//实现类开始报错
public class StuDaoImpl implements StuDao {
    //注入JdbcTemplate
    private JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());


    @Override
    public void add(Stu stu) {
        String sql="insert into student values(?,?,?,?,?)";
        jdbcTemplate.update(sql,stu.getID(),stu.getName(),stu.getCourse(),stu.getClasses(),stu.getScore());
    }

    @Override
    public void delete(int ID,String course) {
        jdbcTemplate.update("DELETE FROM student WHERE id = ? AND course= ?",ID,course);
    }

    @Override
    public void update(Stu stu) {
        jdbcTemplate.update("UPDATE student SET NAME=?,course=?,classes=?,score=? WHERE id =?",
                stu.getName(),stu.getCourse(),stu.getClasses(),stu.getScore(),stu.getID());
    }

    @Override
    public List<Stu> findAll() {
        List<Stu> query = jdbcTemplate.query("select * from student",
                new BeanPropertyRowMapper<Stu>(Stu.class));
        return query;
    }

    @Override
    public List<Stu> findById(int id) {
        String sql="select * from student where id =?";
        List<Stu> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Stu.class), id);
        return query;
    }

    @Override
    public List<Stu> findByCourse(String course) {
        String sql="select * from student where course =?";
        List<Stu> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Stu.class), course);
        return query;
    }

    @Override
    public List<Stu> findByIdAndCourse(int id, String course) {
        String sql="select * from student where id =? and course =?";
        List<Stu> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Stu.class), id, course);
        return query;
    }
    /*
        业务需要根据行数查询,通过行数得到改行的数据
        SELECT * FROM student LIMIT n-1,1
         */
    @Override
    public Stu findByRow(int row) {
        String sql="SELECT * FROM student LIMIT ?,1";
        Stu stu = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Stu.class), row );
        return stu;
    }

	@Override
	public List<String> findAllCourse() {
		String sql="SELECT DISTINCT course FROM student";
		List<String> all=jdbcTemplate.queryForList(sql, String.class);
		return all;
	}

	@Override
	public List<String> findAllClasses() {
		String sql="SELECT DISTINCT classes FROM student";
		List<String> all=jdbcTemplate.queryForList(sql, String.class);
		return all;
	}

	@Override
	public List<Stu> findAllStu() {
		String sql="SELECT  DISTINCT  * FROM student";
		 List<Stu> query = jdbcTemplate.query(sql,
	                new BeanPropertyRowMapper<Stu>(Stu.class));
	        return query;
	}

	public String findAvgCourse(String course) {
		String sql="SELECT AVG(score) FROM student WHERE course =?";
		String avg=jdbcTemplate.queryForObject(sql, String.class,course);
		return avg;
	}

	@Override
	public String findAvgPerson(int id) {
		String sql="SELECT AVG(score) FROM student WHERE id = ?";
		String avg=jdbcTemplate.queryForObject(sql, String.class,id);
		return avg;
	}

	@Override
	public String findSumPerson(int id) {
		String sql="SELECT SUM(score) FROM student WHERE id=?";
		String sum=jdbcTemplate.queryForObject(sql, String.class,id);
		return sum;
		
	}
//	SELECT MAX(score) FROM student WHERE course ='音乐'
//			SELECT MIN(score) FROM student WHERE course ='音乐'
	//SELECT SUM(score) FROM student WHERE id=103

	@Override
	public String findMaxCourse(String course) {
		String sql="SELECT MAX(score) FROM student WHERE course =?";
		String max=jdbcTemplate.queryForObject(sql, String.class,course);
		return max;
	}

	@Override
	public String findMinCourse(String course) {
		String sql="SELECT MIN(score) FROM student WHERE course =?";
		String min=jdbcTemplate.queryForObject(sql, String.class,course);
		return min;
	}

}
